package com.example.demo.checkReport.util;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import com.example.demo.checkReport.entity.CheckForm;
import com.example.demo.checkReport.entity.CheckHeader;


public class MyExcelUtil {
	
	public static HSSFWorkbook getHSSFWorkbook(CheckHeader header,List<CheckForm> list){
		/*String[][] values,*/
		//create a new workbook if have a no workbook
		
		HSSFWorkbook wb = new HSSFWorkbook();
		//设置单元格格式
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);//水平居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		style.setBorderTop(BorderStyle.THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(BorderStyle.THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(BorderStyle.THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderRight(BorderStyle.THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		
		/*1.创建一个工作表*/
		Sheet sheet = wb.createSheet();
		for(int i = 0;i<13;i++){
			sheet.setColumnWidth(i, 3100);
		}
		/*2.创建第一行*/
		
		HSSFCellStyle style1 = wb.createCellStyle();
		style1.setAlignment(HorizontalAlignment.CENTER);//水平居中
		style1.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		Font font1 = wb.createFont();
		font1.setFontName("方正小标宋简体");
		font1.setFontHeightInPoints((short) 14);
		style1.setFont(font1);
		Row row1 = sheet.createRow(0);
		CellRangeAddress row1region = new CellRangeAddress(0,0,0,12);//第一行占一行13列
		setBorderStyle(BorderStyle.THIN,row1region,sheet);
		sheet.addMergedRegion(row1region);
		Cell cell11 = row1.createCell(0);
		cell11.setCellValue("物资盘点表");
		cell11.setCellStyle(style1);
		/*3.创建第二行*/
		Row row2 = sheet.createRow(1);
		HSSFCellStyle style2 = createStyle(style);
		style2.setWrapText(true);
		Font font3 = wb.createFont();
		font3.setFontName("仿宋_GB2312");
		font3.setFontHeightInPoints((short) 10);
		style2.setFont(font3);
		//第二行第一列内容
		CellRangeAddress row21region = new CellRangeAddress(1,1,0,7);
		setBorderStyle(BorderStyle.THIN,row21region,sheet);
		sheet.addMergedRegion(row21region);
		Cell cell21 = row2.createCell(0);
		cell21.setCellValue("单位："+header.getCompanyName());
		cell21.setCellStyle(style2);
		//第二行第二列内容
		CellRangeAddress row22region = new CellRangeAddress(1,1,8,12);
		setBorderStyle(BorderStyle.THIN,row22region,sheet);
		sheet.addMergedRegion(row22region);
		Cell cell22 = row2.createCell(8);
		Font font2 = wb.createFont();  //设置字体格式
		font2.setUnderline(Font.U_SINGLE); //下划线
		HSSFRichTextString richString = null;  
		richString = new HSSFRichTextString("单位:    （盖章）           ".toString());//左4blank，右10blank
		richString.applyFont(3,21,font2);
		/*richString.applyFont(8,15,font);*/
		cell22.setCellValue(richString);
		cell22.setCellStyle(style2);
		/*4.创建第三行*/
		Row row3 = sheet.createRow(2);
		//第三行第一列
		CellRangeAddress row31region = new CellRangeAddress(2,2,0,7);
		setBorderStyle(BorderStyle.THIN,row31region,sheet);
		sheet.addMergedRegion(row31region);
		Cell cell31 = row3.createCell(0);
		cell31.setCellValue("      单位负责人："+header.getCompanyPrincipal()+"          材料负责人： "+header.getKeeper()+"          主盘人："+header.getChecker()+"          ");
		cell31.setCellStyle(style2);
		//第三行第二列
		CellRangeAddress row32region = new CellRangeAddress(2,2,8,12);
		setBorderStyle(BorderStyle.THIN,row32region,sheet);
		sheet.addMergedRegion(row32region);
		Cell cell32 = row3.createCell(8);
		/*Date date =new Date();
		SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
		String time =sdf.format(date);
		String[] strs =time.split("-");
		String Time = strs[0]+"年"+strs[1]+"月"+strs[2]+"日";*/
		System.out.println("数据库中的日期："+header.getCheckDate());
		char[] c = header.getCheckDate().toCharArray();
		String date = "";
		for(int i = 0;i<10;i++){
			date+=c[i];
		}
		String[] strs = date.split("-");
		String Time = strs[0]+"年"+strs[1]+"月"+strs[2]+"日";
		cell32.setCellValue(Time);
		cell32.setCellStyle(style2);
		/*5.创建第四行*/
		Row row4 = sheet.createRow(3);
		//创建第四行第一列
		CellRangeAddress row41region = new CellRangeAddress(3,4,0,0);
		setBorderStyle(BorderStyle.THIN,row41region,sheet);
		sheet.addMergedRegion(row41region);
		Cell cell41 = row4.createCell(0);
		cell41.setCellValue("序号");
		cell41.setCellStyle(style2);
		//创建第四行第二列
		CellRangeAddress row42region = new CellRangeAddress(3,4,1,1);
		setBorderStyle(BorderStyle.THIN,row42region,sheet);
		sheet.addMergedRegion(row42region);
		Cell cell42 = row4.createCell(1);
		cell42.setCellValue("物资名称");
		cell42.setCellStyle(style2);
		//创建第四行第三列
		CellRangeAddress row43region = new CellRangeAddress(3,4,2,2);
		setBorderStyle(BorderStyle.THIN,row43region,sheet);
		sheet.addMergedRegion(row43region);
		Cell cell43 = row4.createCell(2);
		cell43.setCellValue("规格型号");
		cell43.setCellStyle(style2);
		//创建第四行第四列
		CellRangeAddress row44region = new CellRangeAddress(3,4,3,3);
		setBorderStyle(BorderStyle.THIN,row44region,sheet);
		sheet.addMergedRegion(row44region);
		Cell cell44 = row4.createCell(3);
		cell44.setCellValue("计量单位");
		cell44.setCellStyle(style2);
		//创建第四行第五列
		CellRangeAddress row45region = new CellRangeAddress(3,4,4,4);
		setBorderStyle(BorderStyle.THIN,row45region,sheet);
		sheet.addMergedRegion(row45region);
		Cell cell45 = row4.createCell(4);
		cell45.setCellValue("实有数");
		cell45.setCellStyle(style2);
		//创建第四行第六列
		CellRangeAddress row46region = new CellRangeAddress(3,3,5,7);
		setBorderStyle(BorderStyle.THIN,row46region,sheet);
		sheet.addMergedRegion(row46region);
		Cell cell46 = row4.createCell(5);
		cell46.setCellValue("账面数");
		cell46.setCellStyle(style2);
		//创建第四行第七列
		CellRangeAddress row47region = new CellRangeAddress(3,3,8,9);
		setBorderStyle(BorderStyle.THIN,row47region,sheet);
		sheet.addMergedRegion(row47region);
		Cell cell47 = row4.createCell(8);
		cell47.setCellValue("增多");
		cell47.setCellStyle(style2);
		//创建第四行第八列
		CellRangeAddress row48region = new CellRangeAddress(3,3,10,11);
		setBorderStyle(BorderStyle.THIN,row48region,sheet);
		sheet.addMergedRegion(row48region);
		Cell cell48 = row4.createCell(10);
		cell48.setCellValue("减少");
		cell48.setCellStyle(style2);
		//创建第四行第九列
		CellRangeAddress row49region = new CellRangeAddress(3,4,12,12);
		setBorderStyle(BorderStyle.THIN,row49region,sheet);
		sheet.addMergedRegion(row49region);
		Cell cell49 = row4.createCell(12);
		cell49.setCellValue("备注/监盘标记");
		cell49.setCellStyle(style2);
		/*//创建第五行第六列
		
		CellRangeAddress row56region = new CellRangeAddress(4,4,5,5);
		setBorderStyle(BorderStyle.THIN,row56region,sheet);
		sheet.addMergedRegion(row56region);
		Cell cell56 = row5.createCell(5);
		cell56.setCellValue("数量");
		cell56.setCellStyle(style);
		//创建第五行第七列
		CellRangeAddress row57region = new CellRangeAddress(4,4,6,6);
		setBorderStyle(BorderStyle.THIN,row57region,sheet);
		sheet.addMergedRegion(row57region);
		Cell cell57 = row5.createCell(6);
		cell57.setCellValue("单价");
		cell57.setCellStyle(style);
		//创建第五行第八列
		CellRangeAddress row58region = new CellRangeAddress(4,4,7,7);
		setBorderStyle(BorderStyle.THIN,row58region,sheet);
		sheet.addMergedRegion(row58region);
		Cell cell58 = row5.createCell(7);
		cell58.setCellValue("金额");
		cell58.setCellStyle(style);	
		//创建第五行第九列
		CellRangeAddress row59region = new CellRangeAddress(4,4,8,8);
		setBorderStyle(BorderStyle.THIN,row59region,sheet);
		sheet.addMergedRegion(row59region);
		Cell cell59 = row5.createCell(8);
		cell59.setCellValue("数量");
		cell59.setCellStyle(style);
		//创建第五行第十列
		CellRangeAddress row510region = new CellRangeAddress(4,4,9,9);
		setBorderStyle(BorderStyle.THIN,row510region,sheet);
		sheet.addMergedRegion(row510region);
		Cell cell510 = row5.createCell(9);
		cell510.setCellValue("金额");
		cell510.setCellStyle(style);
		//创建第五行第十一列
		CellRangeAddress row511region = new CellRangeAddress(4,4,10,10);
		setBorderStyle(BorderStyle.THIN,row511region,sheet);
		sheet.addMergedRegion(row511region);
		Cell cell511 = row5.createCell(10);
		cell511.setCellValue("数量");
		cell511.setCellStyle(style);
		//创建第五行第十二列
		CellRangeAddress row512region = new CellRangeAddress(4,4,11,11);
		setBorderStyle(BorderStyle.THIN,row512region,sheet);
		sheet.addMergedRegion(row512region);
		Cell cell512 = row5.createCell(11);
		cell512.setCellValue("金额");
		cell512.setCellStyle(style);
		*/
		Row row5 = sheet.createRow(4);
		Cell cellfix = row5.createCell(0);
		for(int i = 0;i<13;i++){
			cellfix = row5.createCell(i);
			if(i==5){
				cellfix.setCellValue("数量");
			}else if(i==6){
				cellfix.setCellValue("单价");
			}else if(i==7){
				cellfix.setCellValue("金额");
			}else if(i==8){
				cellfix.setCellValue("数量");
			}else if(i==9){
				cellfix.setCellValue("金额");
			}else if(i==10){
				cellfix.setCellValue("数量");
			}else if(i==11){
				cellfix.setCellValue("金额");
			}
			cellfix.setCellStyle(style2);
		}
		/*//创建第六行
		Row row6 = sheet.createRow(5);
		CellRangeAddress row6region = new CellRangeAddress(5,5,0,12);
		setBorderStyle(BorderStyle.THIN,row6region,sheet);
		sheet.addMergedRegion(row6region);
		Cell cell6 = row6.createCell(0);
		cell6.setCellValue("67467474");
		cell6.setCellStyle(style);*/
		/*数据行*/
		Row datarow;
		Cell datacell;
		Double sum = 0.0; //总金额
		for(int i = 0;i<list.size();i++){
			datarow = sheet.createRow(i+5);
			Double price = list.get(i).getPrice().doubleValue();
			int booknum = list.get(i).getBookNum();
			int increnum = list.get(i).getIncreNum();
			int decrenum = list.get(i).getDecreNum();
			String zmje="";
			
			for(int j = 0;j<13;j++){
				datacell = datarow.createCell(j);
				if(j==0){
					datacell.setCellValue(list.get(i).getId());
				}else if(j==1){
					datacell.setCellValue(list.get(i).getName());
				}else if(j==2){
					datacell.setCellValue(list.get(i).getSpecification());
				}else if(j==3){
					datacell.setCellValue(list.get(i).getUnit());
				}else if(j==4){
					datacell.setCellValue(list.get(i).getActualNum());
				}else if(j==5){
					datacell.setCellValue(list.get(i).getBookNum());
				}else if(j==6){
					datacell.setCellValue(price);
				}else if(j==7){
					
					zmje = String.valueOf(price*booknum);
					datacell.setCellValue(zmje);
					sum+=price*booknum;
				}else if(j==8){
					datacell.setCellValue(list.get(i).getIncreNum());
				}else if(j==9){
					String zdje = String.valueOf(price*increnum);
					datacell.setCellValue(zdje);
				}else if(j==10){
					datacell.setCellValue(list.get(i).getDecreNum());
				}else if(j==11){
					String jsje = String.valueOf(price*decrenum);
					datacell.setCellValue(jsje);
				}else if(j==12){
					datacell.setCellValue(list.get(i).getRemark());
				}
				datacell.setCellStyle(style2);
			}
			
		}
		
		/*总金额*/
		Row rowsum = sheet.createRow(5+list.size());
		Cell cellsum;
		for(int i = 0;i<13;i++){
			cellsum = rowsum.createCell(i);
			if(i==7){
				cellsum.setCellValue(String.valueOf(sum));
			}else{
				cellsum.setCellValue("");
			}
			cellsum.setCellStyle(style);
		}
		return wb;
		
	}
	
	public static void setBorderStyle(BorderStyle borderStyle, CellRangeAddress region, Sheet sheet) {
	    RegionUtil.setBorderBottom(borderStyle, region, sheet); //下边框
	    RegionUtil.setBorderLeft(borderStyle, region, sheet); //左边框
	    RegionUtil.setBorderRight(borderStyle, region, sheet); //右边框
	    RegionUtil.setBorderTop(borderStyle, region, sheet); //上边框
	}
	
	public static HSSFCellStyle createStyle(HSSFCellStyle style){
		return style;
	}
}